package com.yt.xiao.code.generate.processor;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yt.xiao.code.generate.dal.po.Column;
import com.yt.xiao.code.generate.dal.po.JdbcInfo;
import com.yt.xiao.code.generate.dal.po.Table;

public class MySQLProcessorImpl implements DBProcessor {

    public MySQLProcessorImpl() {
    }

    public MySQLProcessorImpl(String driverClass, String jdbcUrl, String user, String password) {
        this.driverClass = driverClass;
        this.jdbcUrl = jdbcUrl;
        this.user = user;
        this.password = password;
    }

    private String driverClass = "com.mysql.jdbc.Driver";
    private String jdbcUrl;
    private String user;
    private String password;

    private JdbcInfo jdbcInfo;

    private List<String> tableNameList;
    private boolean tableNameSwitch;
    
    /**
     * 表名称前缀
     */
    private String tablePrefix;

    public void setDriverClass(String driverClass) {
        this.driverClass = driverClass;
    }
    public void setJdbcUrl(String jdbcUrl) {
        this.jdbcUrl = jdbcUrl;
    }
    public void setUser(String user) {
        this.user = user;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public void setTablePrefix(String tablePrefix) {
        this.tablePrefix = tablePrefix;
    }

    public void setTableNameList(List<String> tlist) {
        this.tableNameList=tlist;
    }

    public void setTableNameSwitch(boolean flag) {
        this.tableNameSwitch=flag;
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driverClass);
        return DriverManager.getConnection(this.jdbcUrl, this.user, this.password);
    }

    public List<Table> loadTableList() throws ClassNotFoundException, SQLException{
        List<Table> tableList = new ArrayList<Table>();
        Connection conn = this.getConnection();
        this.jdbcInfo = new JdbcInfo(this.jdbcUrl, this.user, this.password, conn.getCatalog());
        PreparedStatement ps = conn.prepareStatement("select * from information_schema.TABLES where TABLE_SCHEMA = ?");
        ps.setString(1, conn.getCatalog());
        ResultSet tblrs = ps.executeQuery();
        /*DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet tblrs = dbmd.getTables(null, "%", "%", new String[] { "TABLE" });*/
        while(tblrs.next()){
            Table table = new Table(tblrs.getString("TABLE_NAME"), tblrs.getString("TABLE_COMMENT"), this.tablePrefix);
            if(this.tableNameList==null || this.tableNameList.size()<1){
                tableList.add(table);
            }else{
                if(this.tableNameSwitch==true){//只包含指定名称的表
                    if(this.tableNameList.contains(table.getCode())){
                        tableList.add(table);
                    }
                }else{//排除掉指定名称的表
                    if(this.tableNameList.contains(table.getCode())==false){
                        tableList.add(table);
                    }
                }
            }
        }
        tblrs.close();
        ps.close();
        for(int i=0;i<tableList.size();i++){
            this.loadColumns(conn, tableList.get(i));
        }
        conn.close();
        return tableList;
    }
    
    private void loadColumns(Connection conn, Table table) throws SQLException{
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet colrs = dbmd.getColumns(conn.getCatalog(), "%", table.getCode(), "%");
        while(colrs.next()){
            /*System.out.println("*****************************************");
            ResultSetMetaData md = colrs.getMetaData();
            int mc = md.getColumnCount();
            for(int i=1;i<mc+1;i++){
                System.out.println(md.getColumnLabel(i) + "\t" + colrs.getObject(i));
            }*/
            Column column = new Column(colrs.getString("COLUMN_NAME"), colrs.getInt("DATA_TYPE"), colrs.getInt("COLUMN_SIZE"), colrs.getString("REMARKS"));
            column.setAutoincrement("yes".equals(colrs.getString("IS_AUTOINCREMENT").toLowerCase()));
            table.addColumn(column);
        }
        colrs.close();
        this.loadColumnsPK(conn, table);
    }
    
    private void loadColumnsPK(Connection conn, Table table) throws SQLException{
        PreparedStatement ps = conn.prepareStatement("select * from information_schema.COLUMNS where TABLE_SCHEMA = ? and TABLE_NAME=? and COLUMN_KEY=? order by ORDINAL_POSITION asc");
        ps.setString(1, conn.getCatalog());
        ps.setString(2, table.getCode());
        ps.setString(3, "PRI");
        ResultSet colrs = ps.executeQuery();
        while(colrs.next()){
            table.setPkColumn(colrs.getString("COLUMN_NAME"));
        }
        colrs.close();
        ps.close();
    }

    public JdbcInfo getJdbcInfo() {
        return this.jdbcInfo;
    }
}
